﻿using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Data;
using ChurchDiary.Common;
using System.Web;
using System.IO;

namespace ChurchDiary.Scheduler.JobData
{
    public static class EmailJobData
    {
        public static SqlConnection _con = new SqlConnection(String.Format(ConfigurationManager.ConnectionStrings["ChurchDiaryLocal"].ConnectionString));
        public static void Connection()
        {
            if (_con.State == ConnectionState.Closed)
            {
                _con.Open();
            }
        }
        public static List<EntityEmail> GetFailedEmail()
        {
            List<EntityEmail> Emails = new List<EntityEmail>();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_Email]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@Mode", "PENDING");
                    _cmd.Parameters.AddWithValue("@Status", 2);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return Emails;
                        }
                        while (OReader.Read())
                        {
                            EntityEmail Email = new EntityEmail();
                            Email.EmailId = (Guid)OReader["EmailId"];
                            Email.EmailFrom = OReader["EmailFrom"].ToString();
                            Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                            Email.EmailBcc = OReader["EmailBcc"].ToString();
                            Email.EmailCC = OReader["EmailCC"].ToString();
                            Email.EmailSubject = OReader["EmailSubject"].ToString();
                            Email.EmailType = OReader["EmailType"].ToString();
                            Email.EmailContent = GetEmailTemplate(OReader["EmailContent"].ToString(), Email.EmailType, Email.EmailId);
                            Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                            Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                            Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                            Email.EmailSourceId = (Guid)OReader["EmailSourceId"];
                            Emails.Add(Email);
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return Emails;
        }
        public static List<EntityEmail> GetPendingEmail()
        {
            List<EntityEmail> Emails = new List<EntityEmail>();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_Email]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@Mode", "PENDING");
                    _cmd.Parameters.AddWithValue("@Status", 0);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return Emails;
                        }
                        while (OReader.Read())
                        {
                            EntityEmail Email = new EntityEmail();
                            Email.EmailId = (Guid)OReader["EmailId"];
                            Email.EmailFrom = OReader["EmailFrom"].ToString();
                            Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                            Email.EmailBcc = OReader["EmailBcc"].ToString();
                            Email.EmailCC = OReader["EmailCC"].ToString();
                            Email.EmailSubject = OReader["EmailSubject"].ToString();
                            Email.EmailType = OReader["EmailType"].ToString();
                            Email.EmailContent = GetEmailTemplate(OReader["EmailContent"].ToString(), Email.EmailType, Email.EmailId);
                            Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                            Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                            Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                            Email.EmailSourceId  = (Guid)OReader["EmailSourceId"];
                            Emails.Add(Email);
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return Emails;
        }
        public static void UpdateEmailStatus(EntityEmail Email)
        {
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[IDU_Email]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@mode", "UPD");
                    _cmd.Parameters.AddWithValue("@EmailId", Email.EmailId);
                    _cmd.Parameters.AddWithValue("@EmaiStatus", Email.EmaiStatus);
                    _cmd.Parameters.AddWithValue("@EmailSendOn", DateTime.Now);
                    _cmd.ExecuteNonQuery();
                }
            }
            catch (Exception Ex)
            {
            }
        }
        public static EntityNewsletter GetNewsletter(Guid LetterId)
        {
            EntityNewsletter Newsletter = new EntityNewsletter();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_NewsLetter]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@Mode", "ONE");
                    _cmd.Parameters.AddWithValue("@NewsLetterId", LetterId);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return Newsletter;
                        }
                        while (OReader.Read())
                        {
                            Newsletter.NewsLetterId = (Guid)OReader["NewsLetterId"];
                            Newsletter.NewsLetterSubject = OReader["NewsLetterSubject"].ToString();
                            Newsletter.NewsLetterContent = OReader["NewsLetterContent"].ToString();
                            Newsletter.CreatedOn = (DateTime)OReader["CreatedOn"];
                            Newsletter.IsDeleted = (bool)OReader["IsDeleted"];
                            Newsletter.NewsLetterStatus = (int)OReader["NewsLetterStatus"];
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return Newsletter;
        }
        public static string GetEmailTemplate(string TemplateContent, string EmailType, Guid EmailId)
        {
            string Template = "";
            switch (EmailType)
            {
                case "LOGIN":
                    Template = GetLoginTemplate(TemplateContent, EmailId);
                    break;
                default:
                    Template = "No Template found";
                    break;
            }
            return "Template";
        }
        public static string GetLoginTemplate(string TemplateContent, Guid EmailId)
        {
            EntityUser User = GetUserInfo(EmailId);
            TemplateContent = TemplateContent.Replace("[USERNAME]", User.UserName);
            TemplateContent = TemplateContent.Replace("[PASSWORD]", User.Password);
            TemplateContent = TemplateContent.Replace("[NAME]", User.Name);
            return TemplateContent;
        }

        public static EntityUser GetUserInfo(Guid UserId)
        {
            EntityUser User = new EntityUser();
            try
            {
                Connection();
                using (SqlCommand _cmd = new SqlCommand("[dbo].[GET_UserInfo]", _con))
                {
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.AddWithValue("@UserId", UserId);
                    using (IDataReader OReader = _cmd.ExecuteReader())
                    {
                        if (OReader == null)
                        {
                            return User;
                        }
                        while (OReader.Read())
                        {
                            User.Userid = (Guid)OReader["Userid"];
                            User.UserName = OReader["UserName"].ToString();
                            User.Password = OReader["Password"].ToString();
                            User.Emailid = OReader["Emailid"].ToString();
                            User.Name = OReader["Name"].ToString();
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            _con.Close();
            return User;
        }

    }
}
